﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Xml;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.Administration;

public partial class Plan_Progress : UserControl
{
    #region Members

    #region SQL
    string _sqlMain =
@"
SELECT Дата, Магазин, Мебель, Одежда, Обувь, Игрушки, [Детское питание] AS Детское_питание, Косметика, Подгузники, Год, Месяц
FROM dbo.vw_a_Plan_2
";
    #endregion SQL

    #region Data
    DataTable tblMain = null;
    #endregion Data

    #endregion Members

    #region Common
    static public string GetAppSetting(String mName)
    {
        String vStr = null;
        SPWebService mSer = SPWebService.ContentService;
        #region Ver 1
        if (mSer != null)
        {
            foreach (SPWebConfigModification mMod in mSer.WebConfigModifications)
            {
                if (mMod.Owner == mName)
                    vStr = mMod.Value;
            }
        }
        if (vStr != null)
        {
            vStr =
                vStr.Substring(
                    vStr.IndexOf("value=") + 7
                    , vStr.Length - 7 - vStr.IndexOf("value=") - 4);
        }
        #endregion Ver 1
        else
        #region Ver 2
        {
            string strPath =
@"C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config";
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(strPath);
            XmlNode node = xmlDoc.SelectSingleNode(@"/configuration/appSettings");
            foreach (XmlNode keynode in node.ChildNodes)
            {
                string keyStr = keynode.Attributes["key"].Value;
                if (keyStr == mName)
                {
                    vStr = keynode.Attributes["value"].Value;
                    break;
                }
            }
        }
        #endregion Ver 2
        if (String.IsNullOrEmpty(vStr))
            vStr = "";
        return vStr;
    }
    string MakeReal(ref Decimal value, DateTime parDT, string parShopName, string parDepart)
    {
        value = 0;
        StringBuilder s = new StringBuilder();
        DataTable tblReal = GetTradeReal(parDT, parShopName, parDepart);
        Decimal d_real = 0;
        if (tblReal != null && tblReal.Rows.Count > 0
            && tblReal.Rows[0]["Сумма реализации"] != DBNull.Value)
        {
            // [Сумма реализации] - [Сумма возвратов] + [Сумма оптовой реализации]
            value = d_real =
                (Decimal)tblReal.Rows[0]["Сумма реализации"]
                    - (Decimal)tblReal.Rows[0]["Сумма возвратов"]
                    - (Decimal)tblReal.Rows[0]["Сумма оптовой реализации"];
            s.Append("<div title='"
                + @"Сумма реализации - " + string.Format("{0:C}", (Decimal)tblReal.Rows[0]["Сумма реализации"])
                + @"&#13;Сумма возвратов - " + string.Format("{0:C}", (Decimal)tblReal.Rows[0]["Сумма возвратов"])
                + @"&#13;Сумма оптовой реализации - " + string.Format("{0:C}", (Decimal)tblReal.Rows[0]["Сумма оптовой реализации"])
                + "'>");
        }
        else
            s.Append("<div title=''>");
        s.Append(string.Format("{0:C}</div>", d_real));
        return s.ToString();
    }
    string MakeItogo(ref Decimal valueItogo, ref Decimal valuePlan, DateTime parDT, DataRow r, string parShopName, string parDepart, string parDeparSmall)
    {
        StringBuilder s = new StringBuilder();
        s.Append(@"<tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #0000FF;' >
        <td align='right' valign='top' >");
        if (r["Игрушки"] != DBNull.Value)
            valuePlan = (Decimal)r[parDeparSmall];
        string strIgrushkiReal = MakeReal(ref valueItogo, parDT, r["Магазин"].ToString(), parDepart);
        s.Append(strIgrushkiReal);
        return s.ToString();
    }
    #endregion Common

    #region Page Methods
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataBind();

            oDate.SelectedIndex = 0;
            oRegion.Checked = true;
            oRegionList.SelectedIndex = 7;
            //oRegionList.SelectedValue = "Юг-3";
        }
        else
        {
        }
    }
    protected void btnRefresh_Click(object sender, EventArgs e)
    {
        //imgProgress.Visible = true;
        //oData.Visible = false;
        string url = Request.Url.AbsoluteUri;
        //Response.Redirect(url + "?re=1");

        GetMain();
        DrawData();

        lblTitle.Text = "Дата отчета - " + oDate.Text;
        if (oRegion.Checked && oRegionList.SelectedIndex > 0)
            lblTitle.Text += " ; Регион - " + oRegionList.Text;

        oData.Visible = true;

        return;
    }
    protected void gw_DataBound(object sender, EventArgs e)
    {
    }
    protected void oDate_SelectedIndexChanged(object sender, EventArgs e)
    {
        oData.Visible = false;
        lblTitle.Text = "Отчет – (для формирования отчета нажмите кнопку - Обновить)";
        lblFoot.Text = "";
    }
    protected void oRegion_CheckedChanged(object sender, EventArgs e)
    {
        oData.Visible = false;
        lblTitle.Text = "Отчет – (для формирования отчета нажмите кнопку - Обновить)";
        lblFoot.Text = "";
    }
    protected void oRegionList_SelectedIndexChanged(object sender, EventArgs e)
    {
        oData.Visible = false;
        lblTitle.Text = "Отчет – (для формирования отчета нажмите кнопку - Обновить)";
        lblFoot.Text = "";
    }
    #endregion Page Methods

    #region Invalidate
    private void DrawData()
    {
        #region Members
        StringBuilder s = new StringBuilder();
        string dateStrTmp = oDate.SelectedValue;
        int Year = Convert.ToInt32(dateStrTmp.Substring(0, 4));
        int Mont = Convert.ToInt32(dateStrTmp.Substring(5));
        DateTime dt = new DateTime(Year, Mont, 01);
        #endregion Members

        #region Title
        s.Append(
@"<table class='style1'>
        <tr style='background-color: #003366; font-size: medium; color: #FFFFFF; font-weight: bold'>
            <td style='color: #FFFF00'>
                Отдел/Магазин</td>
            <td>
                Игрушки </td>
            <td>
                Мебель </td>
            <td>
                Одежда </td>
            <td>
                Косметика </td>
            <td>
                Подгузники </td>
            <td>
                Детское питание</td>
            <td>
                &nbsp;Обувь</td>
            <td style='color: #FFFF00'>
                &nbsp;Итого</td>
        </tr>");

        #endregion Title

        if (tblMain != null)
        {
            #region Members

            int count = 0;

            Decimal d_ItogoPlan_Total = 0;
            Decimal d_ItogoReal_Total = 0;
            Decimal d_ItogoPrognoz_Total = 0;
            Decimal d_ItogoReal_Total_Igrushki = 0;
            Decimal d_ItogoPlan_Total_Igrushki = 0;
            Decimal d_ItogoPrognoz_Total_Igrushki = 0;
            Decimal d_ItogoReal_Total_Mebel = 0;
            Decimal d_ItogoPlan_Total_Mebel = 0;
            Decimal d_ItogoPrognoz_Total_Mebel = 0;
            Decimal d_ItogoReal_Total_Odejda = 0;
            Decimal d_ItogoPlan_Total_Odejda = 0;
            Decimal d_ItogoPrognoz_Total_Odejda = 0;
            Decimal d_ItogoReal_Total_Kosmet = 0;
            Decimal d_ItogoPlan_Total_Kosmet = 0;
            Decimal d_ItogoPrognoz_Total_Kosmet = 0;
            Decimal d_ItogoReal_Total_Podgusn = 0;
            Decimal d_ItogoPlan_Total_Podgusn = 0;
            Decimal d_ItogoPrognoz_Total_Podgusn = 0;
            Decimal d_ItogoReal_Total_DetskPitan = 0;
            Decimal d_ItogoPlan_Total_DetskPitan = 0;
            Decimal d_ItogoPrognoz_Total_DetskPitan = 0;
            Decimal d_ItogoReal_Total_Obuv = 0;
            Decimal d_ItogoPlan_Total_Obuv = 0;
            Decimal d_ItogoPrognoz_Total_Obuv = 0;
            #endregion

            #region foreach
            foreach (DataRow r in tblMain.Rows)
            {
                count++;

                #region Table Title
                s.Append(
@"
<tr>
    <td>
        <table cellpadding='0' cellspacing='0' class='style1'>
            <tr style='border-style: ridge; border-width: thin'>
                <td width='160px' rowspan='4' style='border-style: ridge; border-width: thin; background-color: #a5cbf7; color: #000000; font-size: medium; font-weight: bold;' >
                    &nbsp;");

                s.Append(r["Магазин"].ToString());

                s.Append(
@"</td>


                <td width='80px' style='border-style: ridge; border-width: thin; background-color: #FFFFD2'>
                    &nbsp;Итого
                </td>
            </tr>
            <tr style='border-style: ridge; border-width: thin; background-color: #FFFFD2'>
                <td width='80px' style='border-style: none; background-color: #FFFFD2'>
                    &nbsp;План
                </td>
            </tr>
            <tr style='border-style: ridge; border-width: thin'>
                <td width='80px' style='border-style: ridge; border-width: thin; background-color: #FFFFD2'>
                    &nbsp;Прогноз</td>
            </tr>
            <tr style='border-style: ridge; border-width: thin'>
                <td width='80px' style='border-style: ridge; border-width: thin; background-color: #FFFFD2'>
                    &nbsp;Опережение</td>
            </tr>
        </table>
    </td>
    <td>");

                #endregion Table Title

                #region Игрушки
                s.Append(@"
<table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;'>");
                #region Итго
                Decimal d_igrushkiPlan = 0;
                Decimal d_igrushkiReal = 0;
                Decimal d_igrushkiPrognoz = 0;
                Decimal d_igrushkiPrognoz_Procent = 0;
                string strTmp = MakeItogo(ref d_igrushkiReal, ref d_igrushkiPlan, dt, r, (string)r["Магазин"], "ИГРУШКИ", "Игрушки");
                s.Append(strTmp);
                d_ItogoReal_Total += d_igrushkiReal;
                d_ItogoReal_Total_Igrushki += d_igrushkiReal;
                #endregion Итго
                #region План
                s.Append(
                @"</td>
    </tr>
    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
        <td align='right' valign='top' >
            &nbsp;");
                s.Append(string.Format("{0:C}", d_igrushkiPlan));
                d_ItogoPlan_Total += d_igrushkiPlan;
                d_ItogoPlan_Total_Igrushki += d_igrushkiPlan;
                #endregion План
                #region Прогноз
                s.Append(
                @"</td>
    </tr>
    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
        <td align='right' valign='top' >
            &nbsp;");
                if (GetPrognoz(ref d_igrushkiPrognoz, dt, (string)r["Магазин"], "ИГРУШКИ") == null)
                {
                    s.Append(string.Format("{0:C}", d_igrushkiPrognoz));
                    d_ItogoPrognoz_Total_Igrushki += d_igrushkiPrognoz;
                }
                else
                    s.Append(string.Format("{0:C}", d_igrushkiPrognoz));
                #endregion Прогноз
                #region Опережение
                if (d_igrushkiPlan > 0)
                    d_igrushkiPrognoz_Procent = (d_igrushkiPrognoz - d_igrushkiPlan) / d_igrushkiPlan * 100;
                string clr = "";
                if (d_igrushkiPrognoz_Procent < 0)
                    clr = "; color: #CC3300; ";
                s.Append(
                @"</td>
    </tr>
    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
        <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid" + clr + "'>&nbsp;");
                s.Append(string.Format("{0:0.00}", d_igrushkiPrognoz_Procent));
                s.Append(@"</td></tr>");
                #endregion Опережение
                s.Append(@"</table></td>");
                #endregion Игрушки

                #region Мебель
                s.Append(@"
<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;'>");
                #region Итго
                Decimal d_MebelPlan = 0;
                Decimal d_MebelReal = 0;
                Decimal d_MebelPrognoz = 0;
                Decimal d_MebelPrognoz_Procent = 0;
                string strTmp2 = MakeItogo(ref d_MebelReal, ref d_MebelPlan, dt, r, (string)r["Магазин"], "МЕБЕЛЬ", "Мебель");
                s.Append(strTmp2);
                d_ItogoReal_Total += d_MebelReal;
                d_ItogoReal_Total_Mebel += d_MebelReal;
                #endregion Итго
                #region План
                s.Append(
                @"</td>
                    </tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                        <td align='right' valign='top' >
                            &nbsp;");
                s.Append(string.Format("{0:C}", d_MebelPlan));
                d_ItogoPlan_Total += d_MebelPlan;
                d_ItogoPlan_Total_Mebel += d_MebelPlan;
                #endregion План
                #region Прогноз
                s.Append(@"</td></tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                        <td align='right' valign='top' >&nbsp;");
                if (GetPrognoz(ref d_MebelPrognoz, dt, (string)r["Магазин"], "МЕБЕЛЬ") == null)
                {
                    s.Append(string.Format("{0:C}", d_MebelPrognoz));
                    d_ItogoPrognoz_Total_Mebel += d_MebelPrognoz;
                }
                else
                    s.Append(string.Format("{0:C}", d_MebelPrognoz));
                #endregion Прогноз
                #region Опережение
                if (d_MebelPlan > 0)
                    d_MebelPrognoz_Procent = (d_MebelPrognoz - d_MebelPlan) / d_MebelPlan * 100;
                clr = "";
                if (d_MebelPrognoz_Procent < 0)
                    clr = "; color: #CC3300; ";
                s.Append(
                @"</td>
                    </tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                        <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr + "'>&nbsp;");
                s.Append(string.Format("{0:0.00}", d_MebelPrognoz_Procent));
                s.Append(@"</td></tr></table></td>");
                #endregion Опережение
                #endregion Мебель

                #region Одежда
                s.Append(@"
<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;'>");
                #region Итго
                Decimal d_OdejdaPlan = 0;
                Decimal d_OdejdaReal = 0;
                Decimal d_OdejdaPrognoz = 0;
                Decimal d_OdejdaPrognoz_Procent = 0;
                string strTmp3 = MakeItogo(ref d_OdejdaReal, ref d_OdejdaPlan, dt, r, (string)r["Магазин"], "ОДЕЖДА", "Одежда");
                s.Append(strTmp3);
                d_ItogoReal_Total += d_OdejdaReal;
                d_ItogoReal_Total_Odejda += d_OdejdaReal;
                #endregion Итго
                #region План
                s.Append(
                @"</td>
                    </tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                        <td align='right' valign='top' >
                            &nbsp;");
                s.Append(string.Format("{0:C}", d_OdejdaPlan));
                d_ItogoPlan_Total += d_OdejdaPlan;
                d_ItogoPlan_Total_Odejda += d_OdejdaPlan;
                #endregion План
                #region Прогноз
                s.Append(@"</td></tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                        <td align='right' valign='top' >
                            &nbsp;");
                if (GetPrognoz(ref d_OdejdaPrognoz, dt, (string)r["Магазин"], "ОДЕЖДА") == null)
                {
                    s.Append(string.Format("{0:C}", d_OdejdaPrognoz));
                    d_ItogoPrognoz_Total_Odejda += d_OdejdaPrognoz;
                }
                else
                    s.Append(string.Format("{0:C}", d_OdejdaPrognoz));
                #endregion Прогноз
                #region Опережение
                if (d_OdejdaPlan > 0)
                    d_OdejdaPrognoz_Procent = (d_OdejdaPrognoz - d_OdejdaPlan) / d_OdejdaPlan * 100;
                clr = "";
                if (d_OdejdaPrognoz_Procent < 0)
                    clr = "; color: #CC3300; ";
                s.Append(
                @"</td></tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                        <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr + "'>&nbsp;");
                s.Append(string.Format("{0:0.00}", d_OdejdaPrognoz_Procent));
                s.Append(@"</td></tr>");
                #endregion Опережение
                s.Append(@"</table></td>");
                #endregion Одежда

                #region Косметика
                s.Append(@"
<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;'>");
                #region Итго
                Decimal d_KosmetPlan = 0;
                Decimal d_KosmetReal = 0;
                Decimal d_KosmetPrognoz = 0;
                Decimal d_KosmetPrognoz_Procent = 0;
                string strTmp4 = MakeItogo(ref d_KosmetReal, ref d_KosmetPlan, dt, r, (string)r["Магазин"], "КОСМЕТИКА", "Косметика");
                s.Append(strTmp4);
                d_ItogoReal_Total += d_KosmetReal;
                d_ItogoReal_Total_Kosmet += d_KosmetReal;
                #endregion Итго
                #region План
                s.Append(
                @"</td>
                    </tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                        <td align='right' valign='top' >
                            &nbsp;");
                s.Append(string.Format("{0:C}", d_KosmetPlan));
                d_ItogoPlan_Total += d_KosmetPlan;
                d_ItogoPlan_Total_Kosmet += d_KosmetPlan;
                #endregion План
                #region Прогноз
                s.Append(
                @"</td></tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                        <td align='right' valign='top' >&nbsp;");
                if (GetPrognoz(ref d_KosmetPrognoz, dt, (string)r["Магазин"], "КОСМЕТИКА") == null)
                {
                    s.Append(string.Format("{0:C}", d_KosmetPrognoz));
                    d_ItogoPrognoz_Total_Kosmet += d_KosmetPrognoz;
                }
                else
                    s.Append(string.Format("{0:C}", d_KosmetPrognoz));
                #endregion Прогноз
                #region Опережение
                if (d_KosmetPlan > 0)
                    d_KosmetPrognoz_Procent = (d_KosmetPrognoz - d_KosmetPlan) / d_KosmetPlan * 100;
                clr = "";
                if (d_KosmetPrognoz_Procent < 0)
                    clr = "; color: #CC3300; ";
                s.Append(
                @"</td></tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                        <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr + "'>&nbsp;");
                s.Append(string.Format("{0:0.00}", d_KosmetPrognoz_Procent));
                s.Append(@"</td></tr>");
                #endregion Опережение
                s.Append(@"</table></td>");
                #endregion Косметика

                #region Подгузники
                s.Append(@"
<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;'>");
                #region Итго
                Decimal d_PodgusnPlan = 0;
                Decimal d_PodgusnReal = 0;
                Decimal d_PodgusnPrognoz = 0;
                Decimal d_PodgusnPrognoz_Procent = 0;
                string strTmp5 = MakeItogo(ref d_PodgusnReal, ref d_PodgusnPlan, dt, r, (string)r["Магазин"], "ПОДГУЗНИКИ", "Подгузники");
                s.Append(strTmp5);
                d_ItogoReal_Total += d_PodgusnReal;
                d_ItogoReal_Total_Podgusn += d_PodgusnReal;
                #endregion Итго
                #region План
                s.Append(
                @"</td>
                    </tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                        <td align='right' valign='top' >
                            &nbsp;");
                s.Append(string.Format("{0:C}", d_PodgusnPlan));
                d_ItogoPlan_Total += d_PodgusnPlan;
                d_ItogoPlan_Total_Podgusn += d_PodgusnPlan;
                #endregion План
                #region Прогноз
                s.Append(@"</td></tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                        <td align='right' valign='top' >&nbsp;");
                if (GetPrognoz(ref d_PodgusnPrognoz, dt, (string)r["Магазин"], "ПОДГУЗНИКИ") == null)
                {
                    s.Append(string.Format("{0:C}", d_PodgusnPrognoz));
                    d_ItogoPrognoz_Total_Podgusn += d_PodgusnPrognoz;
                }
                else
                    s.Append(string.Format("{0:C}", d_PodgusnPrognoz));
                #endregion Прогноз
                #region Опережение
                if (d_PodgusnPlan > 0)
                    d_PodgusnPrognoz_Procent = (d_PodgusnPrognoz - d_PodgusnPlan) / d_PodgusnPlan * 100;
                clr = "";
                if (d_PodgusnPrognoz_Procent < 0)
                    clr = "; color: #CC3300; ";
                s.Append(@"</td></tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                        <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr + "'>&nbsp;");
                s.Append(string.Format("{0:0.00}", d_PodgusnPrognoz_Procent));
                s.Append(@"</td></tr>");
                #endregion Опережение
                s.Append(@"</table></td>");
                #endregion Подгузники

                #region Детское питание
                s.Append(@"
<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;'>");
                #region Итго
                Decimal d_DetskPitanPlan = 0;
                Decimal d_DeskPitanReal = 0;
                Decimal d_DetskPitanPrognoz = 0;
                Decimal d_DetskPitanPrognoz_Procent = 0;
                string strTmp6 = MakeItogo(ref d_DeskPitanReal, ref d_DetskPitanPlan, dt, r, (string)r["Магазин"], "ДЕТСКОЕ ПИТАНИЕ", "Детское_питание");
                s.Append(strTmp6);
                d_ItogoReal_Total += d_DeskPitanReal;
                d_ItogoReal_Total_DetskPitan += d_DeskPitanReal;
                #endregion Итго
                #region План
                s.Append(
                @"</td>
                    </tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                        <td align='right' valign='top' >
                            &nbsp;");
                s.Append(string.Format("{0:C}", d_DetskPitanPlan));
                d_ItogoPlan_Total += d_DetskPitanPlan;
                d_ItogoPlan_Total_DetskPitan += d_DetskPitanPlan;
                #endregion План
                #region Прогноз
                s.Append(@"</td></tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                        <td align='right' valign='top' >&nbsp;");
                if (GetPrognoz(ref d_DetskPitanPrognoz, dt, (string)r["Магазин"], "ДЕТСКОЕ ПИТАНИЕ") == null)
                {
                    s.Append(string.Format("{0:C}", d_DetskPitanPrognoz));
                    d_ItogoPrognoz_Total_DetskPitan += d_DetskPitanPrognoz;
                }
                else
                    s.Append(string.Format("{0:C}", d_DetskPitanPrognoz));
                #endregion Прогноз
                #region Опережение
                if (d_DetskPitanPlan > 0)
                    d_DetskPitanPrognoz_Procent = (d_DetskPitanPrognoz - d_DetskPitanPlan) / d_DetskPitanPlan * 100;
                clr = "";
                if (d_DetskPitanPrognoz_Procent < 0)
                    clr = "; color: #CC3300; ";
                s.Append(@"</td></tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                        <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr + "'>&nbsp;");
                s.Append(string.Format("{0:0.00}", d_DetskPitanPrognoz_Procent));
                s.Append(@"</td></tr>");
                #endregion Опережение
                s.Append(@"</table></td>");
                #endregion Детское питание

                #region Обувь
                s.Append(@"
<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;'>");
                #region Итго
                Decimal d_ObuvPlan = 0;
                Decimal d_ObuvReal = 0;
                Decimal d_ObuvPrognoz = 0;
                Decimal d_ObuvPrognoz_Procent = 0;
                string strTmp7 = MakeItogo(ref d_ObuvReal, ref d_ObuvPlan, dt, r, (string)r["Магазин"], "ОБУВЬ", "Обувь");
                s.Append(strTmp7);
                d_ItogoReal_Total += d_ObuvReal;
                d_ItogoReal_Total_Obuv += d_ObuvReal;
                #endregion Итго
                #region План
                s.Append(
                @"</td>
                    </tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                        <td align='right' valign='top' >
                            &nbsp;");
                s.Append(string.Format("{0:C}", d_ObuvPlan));
                d_ItogoPlan_Total += d_ObuvPlan;
                d_ItogoPlan_Total_Obuv += d_ObuvPlan;
                #endregion План
                #region Прогноз
                s.Append(@"</td></tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                        <td align='right' valign='top' >&nbsp;");
                if (GetPrognoz(ref d_ObuvPrognoz, dt, (string)r["Магазин"], "ОБУВЬ") == null)
                {
                    s.Append(string.Format("{0:C}", d_ObuvPrognoz));
                    d_ItogoPrognoz_Total_Obuv += d_ObuvPrognoz;
                }
                else
                    s.Append(string.Format("{0:C}", d_ObuvPrognoz));
                #endregion Прогноз
                #region Опережение
                if (d_ObuvPlan > 0)
                    d_ObuvPrognoz_Procent = (d_ObuvPrognoz - d_ObuvPlan) / d_ObuvPlan * 100;
                clr = "";
                if (d_ObuvPrognoz_Procent < 0)
                    clr = "; color: #CC3300; ";
                s.Append(@"</td></tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                        <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr + "'>&nbsp;");
                s.Append(string.Format("{0:0.00}", d_ObuvPrognoz_Procent));
                s.Append(@"</td></tr>");
                #endregion Опережение
                s.Append(@"</table></td>");
                #endregion Детское питание

                #region Итого
                s.Append(@"
<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;background-color: #FFFFCC;'>");
                #region Итго
                Decimal d_ItogoReal = d_DeskPitanReal + d_igrushkiReal + d_KosmetReal + d_MebelReal + d_ObuvReal + d_OdejdaReal + d_PodgusnReal;
                s.Append(@"<tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #0000FF;' >
        <td align='right' valign='top' >");
                s.Append(string.Format("{0:C}", d_ItogoReal));
                #endregion Итго
                #region План
                Decimal d_ItogoPlan = d_DetskPitanPlan + d_igrushkiPlan + d_KosmetPlan + d_MebelPlan + d_ObuvPlan + d_OdejdaPlan + d_PodgusnPlan;
                s.Append(@"</td></tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                        <td align='right' valign='top' >
                            &nbsp;");
                s.Append(string.Format("{0:C}", d_ItogoPlan));
                #endregion План
                #region Прогноз
                Decimal d_ItogoPrognoz = d_DetskPitanPrognoz + d_igrushkiPrognoz + d_KosmetPrognoz + d_MebelPrognoz + d_ObuvPrognoz + d_OdejdaPrognoz + d_PodgusnPrognoz;
                d_ItogoPrognoz_Total += d_ItogoPrognoz;
                s.Append(@"</td></tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                        <td align='right' valign='top' >&nbsp;");
                s.Append(string.Format("{0:C}", d_ItogoPrognoz));
                #endregion Прогноз
                #region Опережение
                Decimal d_ItogoPrognoz_Procent = 0;
                if (d_ItogoPlan > 0)
                    d_ItogoPrognoz_Procent = (d_ItogoPrognoz - d_ItogoPlan) / d_ItogoPlan * 100;
                clr = "";
                if (d_ItogoPrognoz_Procent < 0)
                    clr = "; color: #CC3300; ";
                s.Append(@"</td></tr>
                    <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                        <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr + "'>&nbsp;");
                s.Append(string.Format("{0:0.00}", d_ItogoPrognoz_Procent));
                s.Append(@"</td></tr>");
                #endregion Опережение
                s.Append(@"</table></td>");
                #endregion Итого

                #region Last Cell
                s.Append(@"<td>&nbsp;</td></tr>");
                #endregion Last Cell

            } // end foreach
            #endregion foreach

            #region Total

            #region Итого Игрушки
            s.Append(@"
<tr>
<td></td> 
<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;background-color: #FFFFCC;'>");
            #region Итго
            s.Append(@"<tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #0000FF;' >
<td align='right' valign='top' >");
            s.Append(string.Format("{0:C}", d_ItogoReal_Total_Igrushki));
            #endregion Итго
            #region План
            s.Append(
            @"</td>
            </tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                <td align='right' valign='top' >
                    &nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPlan_Total_Igrushki));
            #endregion План
            #region Прогноз
            s.Append(
            @"</td>
            </tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                <td align='right' valign='top' >
                    &nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPrognoz_Total_Igrushki));
            #endregion Прогноз
            #region Опережение
            Decimal d_01 = 0;
            if (d_ItogoPlan_Total_Igrushki > 0)
            {
                d_01 = (d_ItogoPrognoz_Total_Igrushki - d_ItogoPlan_Total_Igrushki) / d_ItogoPlan_Total_Igrushki * 100;
            }
            string clr1 = "";
            if (d_01 < 0)
                clr1 = "; color: #CC3300; ";
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr1 + "'>&nbsp;");
            s.Append(string.Format("{0:0.00}", d_01));
            s.Append(@"</td></tr>");
            #endregion Опережение
            s.Append(@"</table></td>");
            #endregion Итого Игрушки

            #region Итого Мебель
            s.Append(@"<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;background-color: #FFFFCC;'>");
            #region Итго
            s.Append(@"<tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #0000FF;' >
<td align='right' valign='top' >");
            s.Append(string.Format("{0:C}", d_ItogoReal_Total_Mebel));
            #endregion Итго
            #region План
            s.Append(
            @"</td>
            </tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                <td align='right' valign='top' >
                    &nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPlan_Total_Mebel));
            #endregion План
            #region Прогноз
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                <td align='right' valign='top' >&nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPrognoz_Total_Mebel));
            #endregion Прогноз
            #region Опережение
            Decimal d_02 = 0;
            if (d_ItogoPlan_Total_Igrushki > 0)
                d_02 = (d_ItogoPrognoz_Total_Mebel - d_ItogoPlan_Total_Mebel) / d_ItogoPlan_Total_Mebel * 100;
            clr1 = "";
            if (d_02 < 0)
                clr1 = "; color: #CC3300; ";
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr1 + "'>&nbsp;");
            s.Append(string.Format("{0:0.00}", d_02));
            s.Append(@"</td></tr>");
            #endregion Опережение
            s.Append(@"</table></td>");
            #endregion Итого Мебель

            #region Итого Одежда
            s.Append(@"<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;background-color: #FFFFCC;'>");
            #region Итго
            s.Append(@"<tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #0000FF;' >
<td align='right' valign='top' >");
            s.Append(string.Format("{0:C}", d_ItogoReal_Total_Odejda));
            #endregion Итго
            #region План
            s.Append(
            @"</td>
            </tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                <td align='right' valign='top' >
                    &nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPlan_Total_Odejda));
            #endregion План
            #region Прогноз
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                <td align='right' valign='top' >&nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPrognoz_Total_Odejda));
            #endregion Прогноз
            #region Опережение
            Decimal d_03 = 0;
            if (d_ItogoPlan_Total_Odejda > 0)
                d_03 = (d_ItogoPrognoz_Total_Odejda - d_ItogoPlan_Total_Odejda) / d_ItogoPlan_Total_Odejda * 100;
            clr1 = "";
            if (d_03 < 0)
                clr1 = "; color: #CC3300; ";
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr1 + "'>&nbsp;");
            s.Append(string.Format("{0:0.00}", d_03));
            s.Append(@"</td></tr>");
            #endregion Опережение
            s.Append(@"</table></td>");
            #endregion Итого Одежда

            #region Итого Косметика
            s.Append(@"<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;background-color: #FFFFCC;'>");
            #region Итго
            s.Append(@"<tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #0000FF;' >
<td align='right' valign='top' >");
            s.Append(string.Format("{0:C}", d_ItogoReal_Total_Kosmet));
            #endregion Итго
            #region План
            s.Append(
            @"</td>
            </tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                <td align='right' valign='top' >
                    &nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPlan_Total_Kosmet));
            #endregion План
            #region Прогноз
            s.Append(
            @"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                <td align='right' valign='top' >
                    &nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPrognoz_Total_Kosmet));
            #endregion Прогноз
            #region Опережение
            Decimal d_04 = 0;
            if (d_ItogoPlan_Total_Odejda > 0)
                d_04 = (d_ItogoPrognoz_Total_Kosmet - d_ItogoPlan_Total_Kosmet) / d_ItogoPlan_Total_Kosmet * 100;
            clr1 = "";
            if (d_04 < 0)
                clr1 = "; color: #CC3300; ";
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr1 + "'>&nbsp;");
            s.Append(string.Format("{0:0.00}", d_04));
            s.Append(@"</td></tr>");
            #endregion Опережение
            s.Append(@"</table></td>");
            #endregion Итого Косметика

            #region Подгузники
            s.Append(@"<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;background-color: #FFFFCC;'>");
            #region Итго
            s.Append(@"<tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #0000FF;' >
<td align='right' valign='top' >");
            s.Append(string.Format("{0:C}", d_ItogoReal_Total_Podgusn));
            #endregion Итго
            #region План
            s.Append(
            @"</td>
            </tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                <td align='right' valign='top' >
                    &nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPlan_Total_Podgusn));
            #endregion План
            #region Прогноз
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                <td align='right' valign='top' >&nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPrognoz_Total_Podgusn));
            #endregion Прогноз
            #region Опережение
            Decimal d_05 = 0;
            if (d_ItogoPlan_Total_Podgusn > 0)
                d_05 = (d_ItogoPrognoz_Total_Podgusn - d_ItogoPlan_Total_Podgusn) / d_ItogoPlan_Total_Podgusn * 100;
            clr1 = "";
            if (d_05 < 0)
                clr1 = "; color: #CC3300; ";
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr1 + "'>&nbsp;");
            s.Append(string.Format("{0:0.00}", d_05));
            s.Append(@"</td></tr>");
            #endregion Опережение
            s.Append(@"</table></td>");
            #endregion Итого Косметика

            #region Итого Детское питание
            s.Append(@"<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;background-color: #FFFFCC;'>");
            #region Итго
            s.Append(@"<tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #0000FF;' >
<td align='right' valign='top' >");
            s.Append(string.Format("{0:C}", d_ItogoReal_Total_DetskPitan));
            #endregion Итго
            #region План
            s.Append(
            @"</td>
            </tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                <td align='right' valign='top' >
                    &nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPlan_Total_DetskPitan));
            #endregion План
            #region Прогноз
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                <td align='right' valign='top' >
                    &nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPrognoz_Total_DetskPitan));
            #endregion Прогноз
            #region Опережение
            Decimal d_06 = 0;
            if (d_ItogoPlan_Total_DetskPitan > 0)
                d_06 = (d_ItogoPrognoz_Total_DetskPitan - d_ItogoPlan_Total_DetskPitan) / d_ItogoPlan_Total_DetskPitan * 100;
            clr1 = "";
            if (d_06 < 0)
                clr1 = "; color: #CC3300; ";
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr1 + "'>&nbsp;");
            s.Append(string.Format("{0:0.00}", d_06));
            s.Append(@"</td></tr>");
            #endregion Опережение
            s.Append(@"</table></td>");
            #endregion Итого Детское питание

            #region Итого Обувь
            s.Append(@"<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;background-color: #FFFFCC;'>");
            #region Итго
            s.Append(@"<tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #0000FF;' >
<td align='right' valign='top' >");
            s.Append(string.Format("{0:C}", d_ItogoReal_Total_Obuv));
            #endregion Итго
            #region План
            s.Append(
            @"</td>
            </tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                <td align='right' valign='top' >
                    &nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPlan_Total_Obuv));
            #endregion План
            #region Прогноз
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                <td align='right' valign='top' >&nbsp;");
            s.Append(string.Format("{0:0.00}", d_ItogoPrognoz_Total_Obuv));
            #endregion Прогноз
            #region Опережение
            Decimal d_07 = 0;
            if (d_ItogoPlan_Total_Obuv > 0)
                d_07 = (d_ItogoPrognoz_Total_Obuv - d_ItogoPlan_Total_Obuv) / d_ItogoPlan_Total_Obuv * 100;
            clr1 = "";
            if (d_07 < 0)
                clr1 = "; color: #CC3300; ";
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr1 + "'>&nbsp;");
            s.Append(string.Format("{0:0.00}", d_07));
            s.Append(@"</td></tr>");
            #endregion Опережение
            s.Append(@"</table></td>");
            #endregion Итого Обувь

            #region Итого ...
            s.Append(@"<td><table cellpadding='0' cellspacing='0' Style='width: 100%;border-color: #3366CC;background-color: #FFFFCC;'>");
            #region Итго
            s.Append(@"<tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #0000FF;' >
<td align='right' valign='top' >");
            s.Append(string.Format("{0:C}", d_ItogoReal_Total));
            #endregion Итго
            #region План
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000000;' >
                <td align='right' valign='top' >&nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPlan_Total));
            #endregion План
            #region Прогноз
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #339933;' >
                <td align='right' valign='top' >
                    &nbsp;");
            s.Append(string.Format("{0:C}", d_ItogoPrognoz_Total));
            #endregion Прогноз
            #region Опережение
            Decimal d_08 = 0;
            if (d_ItogoPlan_Total > 0)
                d_08 = (d_ItogoPrognoz_Total - d_ItogoPlan_Total) / d_ItogoPlan_Total * 100;
            clr1 = "";
            if (d_08 < 0)
                clr1 = "; color: #CC3300; ";
            s.Append(@"</td></tr>
            <tr align='right' valign='top' Style='font-size: medium; font-weight: bold; color: #000099;'>
                <td align='right' valign='top' style='border-bottom-style: solid; border-right-style: solid;" + clr1 + "'>&nbsp;");
            s.Append(string.Format("{0:0.00}", d_08));
            s.Append(@"</td></tr>");
            #endregion Опережение
            s.Append(@"</table></td><tr>");
            #endregion Итого

            #endregion Total

            // foot
            lblFoot.Text = "Всего магазинов - " + count.ToString();

        }

        s.Append(@"</table>");
        oData.Text = s.ToString();
    }
    #endregion Invalidate

    #region Data Methods
    void GetMain()
    {

        if (oDate.SelectedIndex < 0)
            return;
        string whereStr = @" WHERE (Дата = '" + oDate.Text + "')";
        if (oRegion.Checked)
        {
            string RegionStr = oRegionList.SelectedValue;
            RegionStr = RegionStr.Trim();
            if (!string.IsNullOrEmpty(RegionStr))
                whereStr += @" AND (Регион = N'" + oRegionList.SelectedValue + "')";
        }

        try
        {
            using (OleDbConnection conn = new OleDbConnection(GetAppSetting(@"DB04Connect")))
            {
                conn.Open();
                string cmdText = _sqlMain += whereStr;
                cmdText += " ORDER BY Год DESC, Месяц DESC, Магазин";
                using (OleDbCommand cmd = new OleDbCommand(cmdText, conn))
                {
                    OleDbDataAdapter a = new OleDbDataAdapter(cmd);
                    if (tblMain == null)
                        tblMain = new DataTable();
                    a.Fill(tblMain);
                }
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
        catch (Exception err)
        {
            Response.Write(err.Message);
        }
    }
    DataTable GetTradeReal(DateTime parDT, string parShopName, string parDeparment)
    {
        DataTable tblRet = null;
        string departm = parDeparment.ToUpper();
        string dataStr = string.Format("{0,4:0000}-{1,2:00}-{2,2:00} 00:00:00", parDT.Year, parDT.Month, parDT.Day);
        string sql = @"SELECT
  Year
, Month
, магазин
, секция
, [Сумма реализации]
, [Сумма проданного товара в закупочных ценах]
, [Сумма возвратов в закупочных ценах]
, [Сумма возвратов]
, [Сумма оптовой реализации в закупочных ценах]
, [Сумма оптовой реализации]
FROM vw_a_Trade_Progress_Traffic
WHERE (секция = N'" + departm + "') AND (Year = '" + string.Format("{0,4:0000}", parDT.Year) + "') "
                    + " AND (Month = '" + string.Format("{0,2:00}", parDT.Month) + "') "
                    + "AND (магазин = N'" + parShopName + "')";
        if (oDate.SelectedIndex < 0)
            return null;
        try
        {
            using (OleDbConnection conn = new OleDbConnection(GetAppSetting(@"DB04Connect")))
            {
                conn.Open();
                string cmdText = sql;
                using (OleDbCommand cmd = new OleDbCommand(cmdText, conn))
                {
                    OleDbDataAdapter a = new OleDbDataAdapter(cmd);
                    if (tblMain == null)
                        tblMain = new DataTable();
                    tblRet = new DataTable();
                    a.Fill(tblRet);
                }
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
        catch (Exception err)
        {
            Response.Write(err.Message);
        }
        return tblRet;
    }
    Exception GetPrognoz(ref Decimal parValue, DateTime parDT, string parShopName, string parDeparment)
    {
        Exception ret = null;
        if (oDate.SelectedIndex < 0)
            return null;

        parValue = 0;
        int YearFrom = parDT.Year;
        int MonthFrom = parDT.Month;
        TimeSpan ts = new TimeSpan(768, 0, 0);
        DateTime DtTo = new DateTime(YearFrom, MonthFrom, 1) + ts;
        int YearTo = DtTo.Year;
        int MonthTo = DtTo.Month;

        string sql = @"
SELECT
  дата
, [Сумма реализации] AS c1
, [Сумма возвратов] AS c2
, [Сумма оптовой реализации] AS c3
FROM vw_a_Trade_Progress
WHERE   (дата >= '{0,4:0000}-{1,2:00}-01 00:00:00') 
        AND (дата < '{2,2:00}-{3,2:00}-01 00:00:00') 
        AND (магазин = N'{4}') 
        AND (секция = N'{5}')
ORDER BY дата
";
        sql = string.Format(sql, YearFrom, MonthFrom, YearTo, MonthTo, parShopName, parDeparment);

        DataTable tbl = new DataTable();

        try
        {
            using (OleDbConnection conn = new OleDbConnection(GetAppSetting(@"DB04Connect")))
            {
                conn.Open();
                string cmdText = sql;
                using (OleDbCommand cmd = new OleDbCommand(cmdText, conn))
                {
                    OleDbDataAdapter a = new OleDbDataAdapter(cmd);
                    if (tblMain == null)
                        tblMain = new DataTable();
                    a.Fill(tbl);
                }
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
        catch (Exception err)
        {
            Response.Write(err.Message);
            ret = new Exception(err.Message, err);
        }

        // Calculate . . .
        if (ret == null)
        {
            Decimal val_trade_Total = 0;
            int days = 0;
            int daysInMonth = System.DateTime.DaysInMonth(YearFrom, MonthFrom);
            foreach (DataRow r in tbl.Rows)
            {
                Decimal val_trade = (Decimal)r["c1"] - (Decimal)r["c2"] + (Decimal)r["c3"];
                val_trade_Total += val_trade;
                if ((Decimal)r["c1"] != 0)
                    days++;

            }
            if (days > 0)
            {
                Decimal sredn = val_trade_Total / days;
                parValue = sredn * daysInMonth;
            }
        }

        return ret;
    }
    #endregion Data Methods
}

